How-To Convert Your Data From MS SQL 7 to PostgreSQL 7.1

Prequests:
•	MS SQL 7 (of course)
•	Redhat 7.1
•	PostgreSQL 7.1 RPM’s
•	PHPPgAdmin Tarball
•	A HEX editor (I recommend UltraEdit32)
•	PostgreSQL ODBC Client

Directions:
Follow the steps below to convert your SQL Data to PostgreSQL. Any think following a “#” is the exact command to type at the shell prompt

From Your Redhat Machine:
1.	As root, create a postgres user. # adduser postgres
2.	Install the PostgreSQL RPM’s on your machine.
3.	Install PhpPgAdmin
4.	Start Postgre SQL for the first time. # service PostgreSQL start
5.	Set PostgreSQL to run at system Startup. #  chkconfig --add PostgreSQL
6.	Login as the Postgres User # su postgres
7.	Create a Blank Database # createdb Databasename
From your SQL 7 Server or a machine with the SQL administration tools installed:
1.	Install the PostgreSQL ODBC Drivers
2.	Open the Import and Export Data tool. (Part of SQL 7)
3.	On the Source Screen pick your SQL Server and the database you wish to export from.
4.	On the Destination Screen 
a.	Select “Other (ODBC Data Source)” as the destination
b.	Create a new User/System DSN
i.	Type of data source: Either a System or User Data source
ii.	Driver: PostgreSQL
c.	Driver Setup Screen.
i.	Enter the database name from step 6
ii.	Enter the name or IP of the PostgreSQL server
iii.	Do not enter a username or password on this screen
d.	On the Destination screen enter “Postgres” as the username and enter a Space in the Password field
e.	Pick the table(s) you wish to export
f.	Export the tables. You will receive an error about relationship not existing. Just Press Ok.
5.	Now you need to make corrections to the table(s) structure.
a.	Open the PhpPgAdmin site in your browser
b.	Look at the Properties for the table you just imported.
c.	Check for Boolean fields, they were most likely imported as char (1).
d.	Check all other fields to make sure the correct data type was imported
e.	View the dump Structure
f.	Copy the Select statement to the clipboard
g.	Drop the table from the database
h.	Paste your select statement in to the “Run SQL Code” Box
i.	Correct any data types that were wrong
j.	Run the Select Statement
k.	You should know have a correctly structured table
6.	Now you need to Export all your data to a Delimited text File.
a.	Open the Import and Export Data tool. (Part of SQL 7)
b.	On the Source Screen pick your SQL Server and the database you wish to export from.
c.	On the Destination Screen choose Text File, and enter a file name
d.	Pick the table you wish to export
e.	Change the Row Delimiter to {LF} and change the column delimiter to Tab and change quoting to none.
f.	Export the data
7.	You now need to remove any embedded carriage returns.
8.	Open your text file in the Hex Editor (If prompted to convert the file to Dos mode press NO)
9.	View the file in Hex Mode (CTRL+H if you are using UltraEdit32)
10.	You find all existences of  “0D 0A” with “20” This will Replace all the embedded carriage returns with a space.
11.	Next you need to add a NULL Value Search for the values “09 09” and then replace with “09 7C 09” This finds any blank fields and places a | character there.
12.	Save the file
13.	Copy the file to your PostgreSQL server
14.	Use VI or another text editor to add the following line to the beginning of the text file.  “Copy TableName from stdin with null as ‘|’;”
15.	At the end of the file add. “\.”
16.	Save the file and exit the editor
17.	Login as the postgres user. # Su Postgres
18.	Import the file. # Psql tablename > filename
19.	You should now have your table correctly imported.